--exec  sp_Current_inventory 'CY2210PVC-2'
ALTER    proc sp_current_inventory
	@part_number varchar(50) 
As

	declare @process_step_id bigint
	declare @process_type varchar(50)
	declare @process_type_name varchar(50)
	declare @previous_process_step_id bigint
	declare @pType varchar(50)
	declare @parentProductId int

	declare @temp_process_step_id bigint
	declare @temp_location_id int
	declare @temp_qty_out int
	declare @temp_trasit_qty int
	declare @hold_qty int
	declare @temp_flow_cursor CURSOR
	declare @temp_location_cursor CURSOR
	declare @results Table( Process varchar(50),
				Quantity int,
				Hold_Quantity int,
				Flag int)

	select @temp_process_step_id = Product.ProcessStep_ID, @pType = P_Type, @parentProductId = P_Parent_Product_ID from Product, Process_Step where Product.ProcessStep_id = Process_Step.ProcessStep_id and Part_Number = @part_number and PS_Type_Name = 'Finished Goods'

	IF( @pType <> 'CHIP' )
	BEGIN
	 	IF( @parentProductId != -1)
 		BEGIN	
 			SELECT @temp_process_step_id = processStep_ID FROM product WHERE Product_id = @parentProductId
	 	END
 	END


	set @temp_flow_cursor = CURSOR for
	select ProcessStep_Id, PS_Type, Process_Type_name from fn_get_process_flow_by_processstep_id( @temp_process_step_id )

	open @temp_flow_cursor 
		fetch next from @temp_flow_cursor 
		into @process_step_id, @process_type, @process_type_name

		while @@fetch_status = 0
		begin
			if @process_type_name = 'Finished Goods'
			begin
				select @temp_qty_out = ISNULL( sum( isnull( il.IL_Qty_Balance, lt.Qty_In ) ), 0 ) from Inventory_Lot il, Lot_Table lt where lt.ProcessStep_ID =@process_step_id and lt.Lot_Current_Status = 'Running' and lt.Lot_Manual_Hold = 1 and lt.Lot_ID = il.Lot_ID
				select @hold_qty = ISNULL( sum( isnull( il.IL_Qty_Balance, il.IL_Qty_In ) ), 0 ) from Inventory_Lot il, Lot_Table lt where lt.ProcessStep_ID =@process_step_id and ( lt.Lot_Current_Status = 'Hold' Or lt.Lot_Manual_Hold = 0 ) and lt.Lot_ID = il.Lot_ID
				insert into @results(Process, Quantity, Hold_Quantity, Flag ) values(@process_type, @temp_qty_out, @hold_qty, 9)
			end
			ELSE
			 BEGIN
				select @temp_qty_out = isnull(sum(
					(CASE WHEN (@process_type_name='FAB' OR @process_type_name='Wafer Store') then lt.Wafer_Qty_In ELSE lt.Qty_In END)), 0) from Lot_Table lt where lt.ProcessStep_ID =@process_step_id and lt.Lot_Current_Status = 'Running' and lt.Lot_Manual_Hold = 1
				select @temp_trasit_qty = ISNULL( SUM( ISNULL( SHIP_Qty, 0 ) ), 0 ) from Shipped_lot, Lot_table where  Shipped_lot.Lot_id = Lot_table.Lot_Id and Shipped_lot.Status = 'Transit' and  Lot_table.ProcessStep_id = @process_step_id
				select @hold_qty = isnull(sum((CASE WHEN (@process_type_name='FAB' OR @process_type_name='Wafer Store') then lt.Wafer_Qty_In ELSE lt.Qty_In END)), 0) from Lot_Table lt where lt.ProcessStep_ID =@process_step_id and ( lt.Lot_Current_Status = 'Hold' Or lt.Lot_Manual_Hold = 0 )
				SET @temp_qty_out = ISNULL( @temp_qty_out, 0 ) + ISNULL( @temp_trasit_qty, 0 )
				insert into @results(Process, Quantity, Hold_Quantity, Flag ) values(@process_type, @temp_qty_out, @hold_qty, (CASE @process_type WHEN 'FAB' then 1  WHEN 'Wafer Bank' then 2 WHEN 'Fab Option' then 3 WHEN 'Die Bank' then 4  WHEN 'Assembly' then 5 WHEN 'FT' then 6 WHEN 'FPO' then 7 WHEN 'Tape & Reel' then 8 WHEN 'FG' then 9 else 10 end) )
			 END
			fetch next from @temp_flow_cursor 
			into @process_step_id, @process_type, @process_type_name
			SET @temp_qty_out = 0
			SET @temp_trasit_qty = 0
			SET @hold_qty = 0
		end
	close @temp_flow_cursor
	deallocate @temp_flow_cursor

	select * from @results where Quantity > 0 or Hold_Quantity > 0 order by Flag
	return


